package com.augurit.gzcgwsp.webservice.service.impl;

import java.io.IOException;
import java.io.StringWriter;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;
import javax.xml.datatype.XMLGregorianCalendar;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;
import com.augurit.gzcgwsp.webservice.dao.ExChangeDataDao;
import com.augurit.gzcgwsp.webservice.service.IJinpengDataService;

/**
 * 给金鹏提供的接口
 * @author Administrator
 *
 */
@Service
@Transactional
public class JinpengDataServiceImpl implements IJinpengDataService{	
	private static final Logger log=Logger.getLogger(JinpengDataServiceImpl.class);	
	private static final  ExChangeDataDao ExChangeDataDao=new ExChangeDataDao();

	/**
	 * 户外广告的数据
	 * @param pageNo 第几页
	 * @param pageSize  每页多少条记录
	 * @param sDate 开始时间
	 * @param eDate 结束时间
	 * @return
	 */
	@Override
	public String  getHwggDatas(String pageNo, String pageSize,Object sDate,Object eDate) {
		if(StringUtils.isBlank(pageNo)){
			pageNo="1";
		}
		if(StringUtils.isBlank(pageSize)){
			pageSize="10";
		}
		List list=this.getHwgg(pageNo, pageSize, sDate, eDate);
		String sql=this.getHwggSql( sDate, eDate);
		Long totalcount=ExChangeDataDao.getTotalCount(sql);
		Document doc = DocumentHelper.createDocument();  
		Element root = doc.addElement("hwggs");   
	    root.addElement("totalcount").addText(String.valueOf(totalcount));
		if(list.size()>0){
			for(int i=0 ;i<list.size();i++){
				Map map=(Map) list.get(i);
				Element hwgg=root.addElement("hwgg");
				this.creatHwggXml(map, hwgg);
			}
		}
		return this.formatXML(doc, "UTF-8");
	}
	
	/**
	 * 燃气经营许可的数据
	 * @param pageNo 第几页
	 * @param pageSize  每页多少条记录
	 * @param sDate 开始时间
	 * @param eDate 结束时间
	 * @return
	 */
	public String  getRqjyxkDatas(String pageNo, String pageSize,Object sDate,Object eDate) {
		if(StringUtils.isBlank(pageNo)){
			pageNo="1";
		}
		if(StringUtils.isBlank(pageSize)){
			pageSize="10";
		}
		List list=this.getJyxk(pageNo, pageSize,sDate,eDate);
		String sql=this.getJyxkSql(sDate,eDate);
		Long totalcount=ExChangeDataDao.getTotalCount(sql);
		Document doc = DocumentHelper.createDocument();  
		Element root = doc.addElement("jyxks");   
		root.addElement("totalcount").addText(String.valueOf(totalcount));
		if(list.size()>0){
			for(int i=0 ;i<list.size();i++){
				Map map=(Map) list.get(i);
				Element jyxk=root.addElement("jyxk");
			 this.creatJyxkXml(map, jyxk);
			}
		}
		return this.formatXML(doc,"UTF-8");
	}
	
	/**
	 * 陆域的数据
	 * @param pageNo 第几页
	 * @param pageSize  每页多少条记录
	 * @param sDate 开始时间
	 * @param eDate 结束时间
	 * @return
	 */
	public String  getShljqsDatas(String pageNo, String pageSize,Object sDate,Object eDate) {
		if(StringUtils.isBlank(pageNo)){
			pageNo="1";
		}
		if(StringUtils.isBlank(pageSize)){
			pageSize="10";
		}
		List list=this.getShljqs(pageNo, pageSize, sDate, eDate);
		String sql=this.getShljqsSql( sDate, eDate);
		Long totalcount=ExChangeDataDao.getTotalCount(sql);
		Document doc = DocumentHelper.createDocument();  
		Element root = doc.addElement("shljqss");   
		root.addElement("totalcount").addText(String.valueOf(totalcount));
		if(list.size()>0){
			for(int i=0 ;i<list.size();i++){
				Map map=(Map) list.get(i);
				Element shljqs=root.addElement("shljqs");
			 this.creatShljqsXml(map, shljqs);
			}
		}
		return this.formatXML(doc,"UTF-8");
	}
	
	/**
	 * 企业资质数据
	 * @param pageNo
	 * @param pageSize
	 * @param sDate
	 * @param eDate
	 * @return
	 */
	@Override
	public String getRqQyzzDatas(String pageNo, String pageSize,Object sDate,Object eDate) {
		if(StringUtils.isBlank(pageNo)){
			pageNo="1";
		}
		if(StringUtils.isBlank(pageSize)){
			pageSize="10";
		}
		List list=this.getRqQyzz(pageNo, pageSize, sDate, eDate);
		String sql=this.getRqQyzzSql( sDate, eDate);
		Long totalcount=ExChangeDataDao.getTotalCount(sql);
		Document doc = DocumentHelper.createDocument();  
		Element root = doc.addElement("rqQyzzs");   
		root.addElement("totalcount").addText(String.valueOf(totalcount));
		if(list.size()>0){
			for(int i=0 ;i<list.size();i++){
				Map map=(Map) list.get(i);
				Element rqQyzz=root.addElement("rqQyzz");
			 this.creatRqQyzzXml(map, rqQyzz);
			}
		}
		return this.formatXML(doc,"UTF-8");
	}
	
	/**
	 * 水域数据
	 * @param pageNo
	 * @param pageSize
	 * @param sDate
	 * @param eDate
	 * @return
	 */
	@Override
	public String getShljysDatas(String pageNo, String pageSize,Object sDate,Object eDate) {
		if(StringUtils.isBlank(pageNo)){
			pageNo="1";
		}
		if(StringUtils.isBlank(pageSize)){
			pageSize="10";
		}
		List list=this.getShljys(pageNo, pageSize, sDate, eDate);
		String sql=this.getShljysSql( sDate, eDate);
		Long totalcount=ExChangeDataDao.getTotalCount(sql);
		Document doc = DocumentHelper.createDocument();  
		Element root = doc.addElement("shljyss");   
		root.addElement("totalcount").addText(String.valueOf(totalcount));
		if(list.size()>0){
			for(int i=0 ;i<list.size();i++){
				Map map=(Map) list.get(i);
				Element shljys=root.addElement("shljys");
			 this.creatShljysXml(map, shljys);
			}
		}
		return this.formatXML(doc,"UTF-8");
	}
	
	/**
	 * 处置数据
	 * @param pageNo
	 * @param pageSize
	 * @param sDate
	 * @param eDate
	 * @return
	 */
	@Override
	public String getShljczDatas(String pageNo, String pageSize,Object sDate,Object eDate) {
		if(StringUtils.isBlank(pageNo)){
			pageNo="1";
		}
		if(StringUtils.isBlank(pageSize)){
			pageSize="10";
		}
		List list=this.getShljcz(pageNo, pageSize, sDate, eDate);
		String sql=this.getShljczSql( sDate, eDate);
		Long totalcount=ExChangeDataDao.getTotalCount(sql);
		Document doc = DocumentHelper.createDocument();  
		Element root = doc.addElement("shljczs");   
		root.addElement("totalcount").addText(String.valueOf(totalcount));
		if(list.size()>0){
			for(int i=0 ;i<list.size();i++){
				Map map=(Map) list.get(i);
				Element shljcz=root.addElement("shljcz");
			 this.creatShljczXml(map, shljcz);
			}
		}
		return this.formatXML(doc,"UTF-8");
	}
	
	/**
	 * 余泥处 排放数据
	 * @param pageNo
	 * @param pageSize
	 * @param sDate
	 * @param eDate
	 * @return
	 */
	@Override
	public String getYncPfDatas(String pageNo, String pageSize,Object sDate,Object eDate) {
		if(StringUtils.isBlank(pageNo)){
			pageNo="1";
		}
		if(StringUtils.isBlank(pageSize)){
			pageSize="10";
		}
		List list=this.getYncPf(pageNo, pageSize, sDate, eDate);
		String sql=this.getYncPfSql( sDate, eDate);
		Long totalcount=ExChangeDataDao.getTotalCount(sql);
		Document doc = DocumentHelper.createDocument();  
		Element root = doc.addElement("yncPfs");   
		root.addElement("totalcount").addText(String.valueOf(totalcount));
		if(list.size()>0){
			for(int i=0 ;i<list.size();i++){
				Map map=(Map) list.get(i);
				Element yncPf=root.addElement("yncPf");
			 this.creatYncPfXml(map, yncPf);
			}
		}
		return this.formatXML(doc,"UTF-8");
	}
	
	/**
	 * 余泥处 消纳数据
	 * @param pageNo
	 * @param pageSize
	 * @param sDate
	 * @param eDate
	 * @return
	 */
	@Override
	public String getYncXnDatas(String pageNo, String pageSize,Object sDate,Object eDate) {
		if(StringUtils.isBlank(pageNo)){
			pageNo="1";
		}
		if(StringUtils.isBlank(pageSize)){
			pageSize="10";
		}
		List list=this.getYncXn(pageNo, pageSize, sDate, eDate);
		String sql=this.getYncXnSql( sDate, eDate);
		Long totalcount=ExChangeDataDao.getTotalCount(sql);
		Document doc = DocumentHelper.createDocument();  
		Element root = doc.addElement("yncXns");   
		root.addElement("totalcount").addText(String.valueOf(totalcount));
		if(list.size()>0){
			for(int i=0 ;i<list.size();i++){
				Map map=(Map) list.get(i);
				Element yncXn=root.addElement("yncXn");
			 this.creatYncXnXml(map, yncXn);
			}
		}
		return this.formatXML(doc,"UTF-8");
	}
	
	/**
	 * 余泥处 运输数据
	 * @param pageNo
	 * @param pageSize
	 * @param sDate
	 * @param eDate
	 * @return
	 */
	@Override
	public String getYncYsDatas(String pageNo, String pageSize,Object sDate,Object eDate) {
		if(StringUtils.isBlank(pageNo)){
			pageNo="1";
		}
		if(StringUtils.isBlank(pageSize)){
			pageSize="10";
		}
		List list=this.getYncYs(pageNo, pageSize, sDate, eDate);
		String sql=this.getYncYsSql( sDate, eDate);
		Long totalcount=ExChangeDataDao.getTotalCount(sql);
		Document doc = DocumentHelper.createDocument();  
		Element root = doc.addElement("yncYss");   
		root.addElement("totalcount").addText(String.valueOf(totalcount));
		if(list.size()>0){
			for(int i=0 ;i<list.size();i++){
				Map map=(Map) list.get(i);
				Element yncYs=root.addElement("yncYs");
			 this.creatYncYsXml(map, yncYs);
			}
		}
		return this.formatXML(doc,"UTF-8");
	}
	
	/**
	 * 生成户外广告的xml
	 * @param map
	 * @param element
	 */
	private void creatHwggXml(Map map,Element element){
		element.addElement("end_").addText((String)map.get("end_")==null?"":(String)map.get("end_"));//结案时间
		//有效期 拼出来
		String startyear = (String)map.get("startyear");
		String startmonth = (String)map.get("startmonth");
		String startday = (String)map.get("startday");
		String endyear = (String)map.get("endyear");
		String endmonth = (String)map.get("endmonth");
		String endday = (String)map.get("endday");
		String yxrq_start = "";
		if(StringUtils.isNotBlank(startyear) && StringUtils.isNotBlank(startmonth) && StringUtils.isNotBlank(startday)){
			yxrq_start = startyear + "年" + startmonth + "月" + startday + "日";
		}
		element.addElement("yxrq_start").addText(yxrq_start);//有效期开始
		
		String yxrq_end = "";
		if(StringUtils.isNotBlank(endyear) && StringUtils.isNotBlank(endmonth) && StringUtils.isNotBlank(endday)){
			yxrq_end = endyear + "年" + endmonth + "月" + endday + "日";
		}
		element.addElement("yxrq_end").addText(yxrq_end);//有效期结束

		//发证日期  拼出来
		String printyear = (String)map.get("printyear");
		String printmonth = (String)map.get("printmonth");
		String printday = (String)map.get("printday");
		String fzrq = "";
		if(StringUtils.isNotBlank(printyear) && StringUtils.isNotBlank(printmonth) && StringUtils.isNotBlank(printday)){
			fzrq = printyear + "年" + printmonth + "月" + printday + "日";
		}
		element.addElement("fzrq").addText(fzrq);//发证日期
		
		element.addElement("szzh").addText((String)map.get("szzh")==null?"":(String)map.get("szzh"));//设置证号
		element.addElement("sqdw").addText((String)map.get("sqdw")==null?"":(String)map.get("sqdw"));//设置人名称
		element.addElement("ggszd").addText((String)map.get("ggszd")==null?"":(String)map.get("ggszd"));//地址
		element.addElement("frxm").addText((String)map.get("frxm")==null?"":(String)map.get("frxm"));//设置证号
		element.addElement("ggxs").addText((String)map.get("ggxs")==null?"":(String)map.get("ggxs"));//设置类型
		element.addElement("szgg_data").addText((String)map.get("szgg_data")==null?"":(String)map.get("szgg_data"));//设置规格
		element.addElement("bz").addText((String)map.get("bz")==null?"":(String)map.get("bz"));//备注
		element.addElement("fzjg").addText("广州市城市管理委员会");//发证机关		
	}
	
	/**
	 * 生成经营许可的xml
	 * @param map
	 * @param element
	 */
	private void creatJyxkXml(Map map,Element element){
		element.addElement("end_").addText((String)map.get("end_")==null?"":(String)map.get("end_"));//结案时间
		//有效期  拼出来
		String endyear = (String)map.get("endyear");
		String endmonth = (String)map.get("endmonth");
		String endday = (String)map.get("endday");
		String yxq = "";
		if(StringUtils.isNotBlank(endyear) && StringUtils.isNotBlank(endmonth) && StringUtils.isNotBlank(endday)){
			yxq = endyear + "年" + endmonth + "月" + endday + "日";
		}
		element.addElement("yxq").addText(yxq);//有效期
		//发证日期  拼出来
		String startyear = (String)map.get("startyear");
		String startmonth = (String)map.get("startmonth");
		String startday = (String)map.get("startday");
		String fzrq = "";
		if(StringUtils.isNotBlank(startyear) && StringUtils.isNotBlank(startmonth) && StringUtils.isNotBlank(startday)){
			fzrq = startyear + "年" + startmonth + "月" + startday + "日";
		}
		element.addElement("fzrq").addText(fzrq);//发证日期
		
		element.addElement("zsbh").addText((String)map.get("zsbh")==null?"":(String)map.get("zsbh"));//证书编号
		element.addElement("sqdw").addText((String)map.get("sqdw")==null?"":(String)map.get("sqdw"));//企业名称
		element.addElement("address").addText((String)map.get("address")==null?"":(String)map.get("address"));//供气地址
		element.addElement("jyfw").addText((String)map.get("jyfw")==null?"":(String)map.get("jyfw"));//经营范围
		element.addElement("gqqy").addText((String)map.get("gqqy")==null?"":(String)map.get("gqqy"));//供气区域
		element.addElement("fddbr").addText((String)map.get("fddbr")==null?"":(String)map.get("fddbr"));//法定代表人
		element.addElement("fzjg").addText((String)map.get("fzjg")==null?"广州市城市管理委员会":(String)map.get("fzjg"));//发证机关
	}
	
	/**
	 * 生成陆域的xml
	 * @param map
	 * @param element
	 */
	private void creatShljqsXml(Map map,Element element){
		element.addElement("end_").addText((String)map.get("end_")==null?"":(String)map.get("end_"));//结案时间
		//有效期开始  拼出来
		String startyear = (String)map.get("startyear");
		String startmonth = (String)map.get("startmonth");
		String startday = (String)map.get("startday");
		String yxq_start = "";
		if(StringUtils.isNotBlank(startyear) && StringUtils.isNotBlank(startmonth) && StringUtils.isNotBlank(startday)){
			yxq_start = startyear + "年" + startmonth + "月" + startday + "日";
		}
		element.addElement("yxq_start").addText(yxq_start);//有效期开始
		
		//有效期结束  拼出来
		String endyear = (String)map.get("endyear");
		String endmonth = (String)map.get("endmonth");
		String endday = (String)map.get("endday");
		String yxq_end = "";
		if(StringUtils.isNotBlank(endyear) && StringUtils.isNotBlank(endmonth) && StringUtils.isNotBlank(endday)){
			yxq_end = endyear + "年" + endmonth + "月" + endday + "日";
		}
		element.addElement("yxq_end").addText(yxq_end);//有效期结束
		
		
		element.addElement("qymc").addText((String)map.get("qymc")==null?"":(String)map.get("qymc"));//公司名称
		element.addElement("xkcontent").addText((String)map.get("xkcontent")==null?"":(String)map.get("xkcontent"));//许可内容
		element.addElement("projectname").addText("");//项目名称
		element.addElement("zsbh").addText((String)map.get("zsbh")==null?"":(String)map.get("zsbh"));//证书编号
		element.addElement("jdtel").addText((String)map.get("jdtel")==null?"":(String)map.get("jdtel"));//监督电话
		element.addElement("fzjg").addText((String)map.get("fzjg")==null?"广州市城市管理委员会":(String)map.get("fzjg"));//发证机关
	}
	
	/**
	 * 生成企业资质的xml
	 * @param map
	 * @param element
	 */
	private void creatRqQyzzXml(Map map,Element element){
		element.addElement("end_").addText((String)map.get("end_")==null?"":(String)map.get("end_"));//结案时间
		//发证日期  拼出来
		String year = (String)map.get("year");
		String month = (String)map.get("month");
		String day = (String)map.get("day");
		String fzrq = "";
		if(StringUtils.isNotBlank(year) && StringUtils.isNotBlank(month) && StringUtils.isNotBlank(day)){
			fzrq = year + "年" + month + "月" + day + "日";
		}
		element.addElement("fzrq").addText(fzrq);//有效期开始
		
		element.addElement("sqdw").addText((String)map.get("sqdw")==null?"":(String)map.get("sqdw"));//企业名称
		element.addElement("zzdj").addText((String)map.get("zzdj")==null?"":(String)map.get("zzdj"));//许可内容
		element.addElement("zsbh").addText((String)map.get("zsbh")==null?"":(String)map.get("zsbh"));//证书编号
		element.addElement("fzjg").addText((String)map.get("fzjg")==null?"广州市城市管理委员会":(String)map.get("fzjg"));//发证机关
	}
	
	/**
	 * 生成水域的xml
	 * @param map
	 * @param element
	 */
	private void creatShljysXml(Map map,Element element){
		
		element.addElement("end_").addText((String)map.get("end_")==null?"":(String)map.get("end_"));//结案时间
		//有效期开始  拼出来
		String startyear = (String)map.get("startyear");
		String startmonth = (String)map.get("startmonth");
		String startday = (String)map.get("startday");
		String yxq_start = "";
		if(StringUtils.isNotBlank(startyear) && StringUtils.isNotBlank(startmonth) && StringUtils.isNotBlank(startday)){
			yxq_start = startyear + "年" + startmonth + "月" + startday + "日";
		}
		element.addElement("yxq_start").addText(yxq_start);//有效期开始
		
		//有效期结束  拼出来
		String endyear = (String)map.get("endyear");
		String endmonth = (String)map.get("endmonth");
		String endday = (String)map.get("endday");
		String yxq_end = "";
		if(StringUtils.isNotBlank(endyear) && StringUtils.isNotBlank(endmonth) && StringUtils.isNotBlank(endday)){
			yxq_end = endyear + "年" + endmonth + "月" + endday + "日";
		}
		element.addElement("yxq_end").addText(yxq_end);//有效期结束
		
		//发证日期  拼出来
		String fzyear = (String)map.get("fzyear");
		String fzmonth = (String)map.get("fzmonth");
		String fzday = (String)map.get("fzday");
		String fzrq = "";
		if(StringUtils.isNotBlank(fzyear) && StringUtils.isNotBlank(fzmonth) && StringUtils.isNotBlank(fzday)){
			fzrq = fzyear + "年" + fzmonth + "月" + fzday + "日";
		}
		element.addElement("fzrq").addText(fzrq);//发证日期
		
		element.addElement("qymc").addText((String)map.get("qymc")==null?"":(String)map.get("qymc"));//公司名称
		element.addElement("xkcontent").addText((String)map.get("xkcontent")==null?"":(String)map.get("xkcontent"));//许可内容
		element.addElement("zsbh").addText((String)map.get("zsbh")==null?"":(String)map.get("zsbh"));//证书编号
		element.addElement("jdtel").addText((String)map.get("jdtel")==null?"":(String)map.get("jdtel"));//监督电话
		element.addElement("fzjg").addText((String)map.get("fzjg")==null?"广州市城市管理委员会":(String)map.get("fzjg"));//发证机关
	}
	
	/**
	 * 生成处置的xml
	 * @param map
	 * @param element
	 */
	private void creatShljczXml(Map map,Element element){
		
		element.addElement("end_").addText((String)map.get("end_")==null?"":(String)map.get("end_"));//结案时间
		//有效期开始  拼出来
		String startyear = (String)map.get("startyear");
		String startmonth = (String)map.get("startmonth");
		String startday = (String)map.get("startday");
		String yxq_start = "";
		if(StringUtils.isNotBlank(startyear) && StringUtils.isNotBlank(startmonth) && StringUtils.isNotBlank(startday)){
			yxq_start = startyear + "年" + startmonth + "月" + startday + "日";
		}
		element.addElement("yxq_start").addText(yxq_start);//有效期开始
		
		//有效期结束  拼出来
		String endyear = (String)map.get("endyear");
		String endmonth = (String)map.get("endmonth");
		String endday = (String)map.get("endday");
		String yxq_end = "";
		if(StringUtils.isNotBlank(endyear) && StringUtils.isNotBlank(endmonth) && StringUtils.isNotBlank(endday)){
			yxq_end = endyear + "年" + endmonth + "月" + endday + "日";
		}
		element.addElement("yxq_end").addText(yxq_end);//有效期结束
		
		//发证日期  拼出来
		String fzyear = (String)map.get("fzyear");
		String fzmonth = (String)map.get("fzmonth");
		String fzday = (String)map.get("fzday");
		String fzrq = "";
		if(StringUtils.isNotBlank(fzyear) && StringUtils.isNotBlank(fzmonth) && StringUtils.isNotBlank(fzday)){
			fzrq = fzyear + "年" + fzmonth + "月" + fzday + "日";
		}
		element.addElement("fzrq").addText(fzrq);//发证日期
		
		element.addElement("qymc").addText((String)map.get("qymc")==null?"":(String)map.get("qymc"));//公司名称
		element.addElement("xkcontent").addText((String)map.get("xkcontent")==null?"":(String)map.get("xkcontent"));//许可内容
		element.addElement("zsbh").addText((String)map.get("zsbh")==null?"":(String)map.get("zsbh"));//证书编号
		element.addElement("jdtel").addText((String)map.get("jdtel")==null?"":(String)map.get("jdtel"));//监督电话
		element.addElement("fzjg").addText((String)map.get("fzjg")==null?"广州市城市管理委员会":(String)map.get("fzjg"));//发证机关
	}
	
	/**
	 * 生成余泥处 排放的xml
	 * @param map
	 * @param element
	 */
	private void creatYncPfXml(Map map,Element element){
		element.addElement("end_").addText((String)map.get("end_")==null?"":(String)map.get("end_"));//结案时间
		element.addElement("zsno").addText((String)map.get("zsno")==null?"":(String)map.get("zsno"));//NO
		element.addElement("zsbh").addText((String)map.get("zsbh")==null?"":(String)map.get("zsbh"));//号
		element.addElement("fzdw").addText((String)map.get("fzdw")==null?"":(String)map.get("fzdw"));//发证单位
		element.addElement("fzrq").addText((String)map.get("fzrq")==null?"":(String)map.get("fzrq"));//发证日期
		element.addElement("sqdw").addText((String)map.get("sqdw")==null?"":(String)map.get("sqdw"));//建设单位
		element.addElement("jsdw_lxr").addText((String)map.get("jsdw_lxr")==null?"":(String)map.get("jsdw_lxr"));//联系人
		element.addElement("jsdw_lxr_lxdh").addText((String)map.get("jsdw_lxr_lxdh")==null?"":(String)map.get("jsdw_lxr_lxdh"));//联系电话		
		
		element.addElement("gcmc").addText((String)map.get("gcmc")==null?"":(String)map.get("gcmc"));//工程名称
		element.addElement("gcdz").addText((String)map.get("gcdz")==null?"":(String)map.get("gcdz"));//工程地址
		element.addElement("pfczl").addText((String)map.get("pfczl")==null?"":(String)map.get("pfczl"));//排放处置量
		element.addElement("xkfw").addText("排放建筑废弃物");//许可范围
		element.addElement("yxrq_start").addText((String)map.get("ysgs_kgrq")==null?"":(String)map.get("ysgs_kgrq"));//有效期开始
		element.addElement("yxrq_end").addText((String)map.get("ysgs_wgrq")==null?"":(String)map.get("ysgs_wgrq"));//有效期结束
	}
	
	/**
	 * 生成余泥处 消纳的xml
	 * @param map
	 * @param element
	 */
	private void creatYncXnXml(Map map,Element element){
		element.addElement("end_").addText((String)map.get("end_")==null?"":(String)map.get("end_"));//结案时间
		element.addElement("zsno").addText((String)map.get("zsno")==null?"":(String)map.get("zsno"));//NO
		element.addElement("zsbh").addText((String)map.get("zsbh")==null?"":(String)map.get("zsbh"));//号
		element.addElement("fzdw").addText((String)map.get("fzdw")==null?"":(String)map.get("fzdw"));//发证单位
		element.addElement("fzrq").addText((String)map.get("fzrq")==null?"":(String)map.get("fzrq"));//发证日期
		element.addElement("sqdw").addText((String)map.get("sqdw")==null?"":(String)map.get("sqdw"));//建设单位
		element.addElement("jsdw_lxr").addText((String)map.get("jsdw_lxr")==null?"":(String)map.get("jsdw_lxr"));//联系人
		element.addElement("jsdw_lxr_lxdh").addText((String)map.get("jsdw_lxr_lxdh")==null?"":(String)map.get("jsdw_lxr_lxdh"));//联系电话		
		element.addElement("gcmc").addText((String)map.get("gcmc")==null?"":(String)map.get("gcmc"));//工程名称
		element.addElement("gcdz").addText((String)map.get("gcdz")==null?"":(String)map.get("gcdz"));//工程地址
		element.addElement("pfczl").addText((String)map.get("pfczl")==null?"":(String)map.get("pfczl"));//排放处置量
		element.addElement("xkfw").addText("消纳建筑废弃物");//许可范围
		element.addElement("yxrq_start").addText((String)map.get("ysgs_kgrq")==null?"":(String)map.get("ysgs_kgrq"));//有效期开始
		element.addElement("yxrq_end").addText((String)map.get("ysgs_wgrq")==null?"":(String)map.get("ysgs_wgrq"));//有效期结束
	}
	
	/**
	 * 生成余泥处 运输的xml
	 * @param map
	 * @param element
	 */
	private void creatYncYsXml(Map map,Element element){		
		element.addElement("end_").addText((String)map.get("end_")==null?"":(String)map.get("end_"));//结案时间
		element.addElement("certificate_no").addText((String)map.get("certificate_no")==null?"":(String)map.get("certificate_no"));//号
		element.addElement("sqdw").addText((String)map.get("sqdw")==null?"":(String)map.get("sqdw"));//企业名称
		element.addElement("address").addText((String)map.get("address")==null?"":(String)map.get("address"));//单位地址
		element.addElement("frxm").addText((String)map.get("frxm")==null?"":(String)map.get("frxm"));//法定代表人姓名
		element.addElement("xkfw").addText((String)map.get("xkfw")==null?"":(String)map.get("xkfw"));//许可范围
		element.addElement("valid_starttime").addText((String)map.get("valid_starttime")==null?"":((String)map.get("valid_starttime")).substring(0, 10));//有效期
		element.addElement("valid_endtime").addText((String)map.get("valid_endtime")==null?"":((String)map.get("valid_endtime")).substring(0, 10));//发证日期
	}

	/**
	 * 户外广告的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getHwgg(String pageNo, String pageSize,Object sDate,Object eDate){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getHwggSql( sDate, eDate);
		return this.ExChangeDataDao.searchByPage(sql, page, null);
		
	}
	
	/**
	 * 经营许可的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getJyxk(String pageNo, String pageSize,Object sDate,Object eDate){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getJyxkSql(sDate,eDate);
		return this.ExChangeDataDao.searchByPage(sql, page, null);		
	}
	
	/**
	 * 陆域的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getShljqs(String pageNo, String pageSize,Object sDate,Object eDate){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getShljqsSql( sDate, eDate);
		return this.ExChangeDataDao.searchByPage(sql, page, null);		
	}
	
	/**
	 * 企业资质的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getRqQyzz(String pageNo, String pageSize,Object sDate,Object eDate){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getRqQyzzSql( sDate, eDate);
		return this.ExChangeDataDao.searchByPage(sql, page, null);		
	}
	
	/**
	 * 水域的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getShljys(String pageNo, String pageSize,Object sDate,Object eDate){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getShljysSql( sDate, eDate);
		return this.ExChangeDataDao.searchByPage(sql, page, null);		
	}
	
	/**
	 * 处置的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getShljcz(String pageNo, String pageSize,Object sDate,Object eDate){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getShljczSql( sDate, eDate);
		return this.ExChangeDataDao.searchByPage(sql, page, null);		
	}
	
	/**
	 * 余泥处 排放的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getYncPf(String pageNo, String pageSize,Object sDate,Object eDate){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getYncPfSql( sDate, eDate);
		return this.ExChangeDataDao.searchByPage(sql, page, null);		
	}
	
	/**
	 * 余泥处 消纳的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getYncXn(String pageNo, String pageSize,Object sDate,Object eDate){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getYncXnSql( sDate, eDate);
		return this.ExChangeDataDao.searchByPage(sql, page, null);		
	}
	
	/**
	 * 余泥处 运输的数据
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	private List getYncYs(String pageNo, String pageSize,Object sDate,Object eDate){
		Page page=new Page();
		page.setPageNo(Integer.valueOf(pageNo));
		page.setPageSize(Integer.valueOf(pageSize));
		String sql=this.getYncYsSql( sDate, eDate);
		return this.ExChangeDataDao.searchByPage(sql, page, null);		
	}
	
	/**
	 * 户外广告的sql
	 * @return
	 */
	private String getHwggSql(Object sDate,Object eDate){
		StringBuffer sb=new StringBuffer();
		sb.append("select distinct TO_CHAR(jhp.end_,'YYYY-MM-DD HH24:mi:ss') end_,t.szzh,t.sqdw,t.ggszd,q.frxm,t.ggxs,t.szgg_data,");
		sb.append("t.startyear,t.startmonth,t.startday,t.endyear,t.endmonth,t.endday,t.bz,t.printyear,t.printmonth,t.printday,t.id");
		sb.append(" from YW_JG_HWGG_SQB t,YW_COM_QYXX q,JBPM4_HIST_PROCINST jhp,WF_BUS_INSTANCE wbi ");
		sb.append(" where t.szzh is not null and q.id=t.qyid and jhp.id_=wbi.proc_inst_id and wbi.bus_memo3=t.accept_no and jhp.end_ is not null ");
		sb = this.selectByDate(sb, sDate, eDate);
		return sb.toString();
	}
	
	
	
	private String getJyxkSql(Object sDate,Object eDate){
		StringBuffer sb=new StringBuffer();
		sb.append("select distinct TO_CHAR(jhp.end_,'YYYY-MM-DD HH24:mi:ss') end_,t.zsbh,t.sqdw,t.jyfw,q.address,");
		sb.append(" t.gqqy,t.fddbr,t.endyear,t.endmonth,t.endday,t.fzjg,t.startyear,t.startmonth,t.startday,t.id");
        sb.append(" from YW_RQ_QYJYXK t,yw_com_qyxx q,JBPM4_HIST_PROCINST jhp,WF_BUS_INSTANCE wbi ");
        sb.append(" where t.zsbh is not null and t.qyid=q.id and jhp.id_=wbi.proc_inst_id and wbi.bus_memo3=t.accept_no and jhp.end_ is not null ");
        sb = this.selectByDate(sb, sDate, eDate);
		return sb.toString();
	}
	
	private String getShljqsSql(Object sDate,Object eDate){
		StringBuffer sb=new StringBuffer();
		sb.append("select distinct TO_CHAR(jhp.end_,'YYYY-MM-DD HH24:mi:ss') end_,t.qymc,t.xkcontent,t.projectname, ");
		sb.append(" t.zsbh,t.startyear,t.startmonth,t.startday,t.endyear,t.endmonth,t.endday,t.fzjg,t.jdtel,t.id ");
		sb.append(" from HW_SHLJQS_V1 t,JBPM4_HIST_PROCINST jhp,WF_BUS_INSTANCE wbi ");
		sb.append(" where t.zsbh is not null and jhp.id_=wbi.proc_inst_id and wbi.bus_memo3=t.accept_no and jhp.end_ is not null ");
		sb = this.selectByDate(sb, sDate, eDate);
		return sb.toString();
	}
	
	private String getRqQyzzSql(Object sDate,Object eDate){
		StringBuffer sb=new StringBuffer();
		sb.append("select distinct TO_CHAR(jhp.end_,'YYYY-MM-DD HH24:mi:ss') end_,t.sqdw,q.zzdj,q.zsbh,q.year,q.month,q.day,t.id ");
		sb.append(" from YW_RQ_QYZZ_INFO t,yw_com_qyxx q ,JBPM4_HIST_PROCINST jhp,WF_BUS_INSTANCE wbi ");
		sb.append(" where t.qyid = q.id and q.zsbh is not null and jhp.id_=wbi.proc_inst_id and wbi.bus_memo3=t.accept_no and jhp.end_ is not null ");
		sb = this.selectByDate(sb, sDate, eDate);
		return sb.toString();
	}
	
	private String getShljysSql(Object sDate,Object eDate){
		StringBuffer sb=new StringBuffer();
		sb.append("select distinct TO_CHAR(jhp.end_,'YYYY-MM-DD HH24:mi:ss') end_, t.qymc,t.xkcontent,t.zsbh,t.startyear, ");
		sb.append(" t.startmonth,t.startday,t.endyear,t.endmonth,t.endday,t.fzyear,t.fzmonth,t.fzday,t.fzjg,t.jdtel,t.id");
		sb.append(" from SYSHLJ_V1 t,JBPM4_HIST_PROCINST jhp,WF_BUS_INSTANCE wbi ");
		sb.append(" where t.zsbh is not null and jhp.id_=wbi.proc_inst_id and wbi.bus_memo3=t.accept_no and jhp.end_ is not null ");
		sb = this.selectByDate(sb, sDate, eDate);
		return sb.toString();
	}
	
	private String getShljczSql(Object sDate,Object eDate){
		StringBuffer sb=new StringBuffer();
		sb.append("select distinct TO_CHAR(jhp.end_,'YYYY-MM-DD HH24:mi:ss') end_, t.qymc,t.xkcontent,t.zsbh,t.startyear, ");
		sb.append(" t.startmonth,t.startday,t.endyear,t.endmonth,t.endday,t.fzyear,t.fzmonth,t.fzday,t.fzjg,t.jdtel,t.id");
		sb.append(" from SHLJ_CZ_V1 t,JBPM4_HIST_PROCINST jhp,WF_BUS_INSTANCE wbi ");
		sb.append(" where t.zsbh is not null and jhp.id_=wbi.proc_inst_id and wbi.bus_memo3=t.accept_no and jhp.end_ is not null ");
		sb = this.selectByDate(sb, sDate, eDate);
		return sb.toString();
	}
	
	private String getYncPfSql(Object sDate,Object eDate){
		StringBuffer sb=new StringBuffer();
		sb.append("select distinct TO_CHAR(jhp.end_,'YYYY-MM-DD HH24:mi:ss') end_,t.zsno,t.zsbh,t.fzdw,t.fzrq, ");
		sb.append(" t.sqdw,t.jsdw_lxr,t.jsdw_lxr_lxdh,t.gcmc,t.gcdz,t.pfczl,t.ysgs_kgrq,t.ysgs_wgrq,t.id");
		sb.append(" from YW_YN_JZFQWCZC_PFSN t,JBPM4_HIST_PROCINST jhp,WF_BUS_INSTANCE wbi ");
		sb.append(" where t.zsno is not null and t.zsbh is not null and jhp.id_=wbi.proc_inst_id and wbi.bus_memo3=t.accept_no and jhp.end_ is not null ");
		sb = this.selectByDate(sb, sDate, eDate);
		return sb.toString();
	}
	
	private String getYncXnSql(Object sDate,Object eDate){
		StringBuffer sb=new StringBuffer();
		sb.append("select distinct TO_CHAR(jhp.end_,'YYYY-MM-DD HH24:mi:ss') end_,t.zsno,t.zsbh,t.fzdw,t.fzrq, ");
		sb.append(" t.sqdw,t.jsdw_lxr,t.jsdw_lxr_lxdh,t.gcmc,t.gcdz,t.pfczl,t.ysgs_kgrq,t.ysgs_wgrq,t.id");
		sb.append(" from YW_YN_JZFQWCZC_XN t,JBPM4_HIST_PROCINST jhp,WF_BUS_INSTANCE wbi ");
		sb.append(" where t.zsno is not null and t.zsbh is not null and jhp.id_=wbi.proc_inst_id and wbi.bus_memo3=t.accept_no and jhp.end_ is not null ");
		sb = this.selectByDate(sb, sDate, eDate);
		return sb.toString();
	}
	
	private String getYncYsSql(Object sDate,Object eDate){
		StringBuffer sb=new StringBuffer();
		sb.append("select distinct TO_CHAR(jhp.end_,'YYYY-MM-DD HH24:mi:ss') end_,t.certificate_no, ");
		sb.append("t.sqdw,t.address,t.frxm,t.xkfw,t.valid_starttime,t.valid_endtime,t.id");
		sb.append(" from YW_YN_JZFQWCZC_JBXX t,JBPM4_HIST_PROCINST jhp,WF_BUS_INSTANCE wbi ");
		sb.append(" where t.certificate_no is not null and jhp.id_=wbi.proc_inst_id and wbi.bus_memo3=t.accept_no and jhp.end_ is not null ");
		sb = this.selectByDate(sb, sDate, eDate);
		return sb.toString();
	}
	
	/**
	 * 增加按日期查询
	 * @param sb
	 * @param sDate
	 * @param eDate
	 * @return
	 */
	public StringBuffer selectByDate(StringBuffer sb,Object sDate,Object eDate){
		SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		if(sDate != null){
        	String inputSDateClass = sDate.getClass().toString();
        	if(inputSDateClass.contains("XMLGregorianCalendarImpl")){
        		String startDateStr = sf.format(getDateFromXMLGregorianCalendar((XMLGregorianCalendar)sDate));
            	sb.append(" and jhp.end_ >= to_date('"+startDateStr+"','yyyy-MM-dd HH24:mi:ss') ");
        	}        	
        }
        if(eDate != null){
        	String inputEDateClass = eDate.getClass().toString();
        	if(inputEDateClass.contains("XMLGregorianCalendarImpl")){
        		String endDateStr = sf.format(getDateFromXMLGregorianCalendar((XMLGregorianCalendar)eDate));
            	sb.append(" and jhp.end_ <= to_date('"+endDateStr+"','yyyy-MM-dd HH24:mi:ss') ");
        	}        	
        }
        sb.append(" order by t.id desc");
        System.out.println("sb = "+sb.toString());
		return sb;
	}

	/**
	 * 根据Date格式日期获取XMLGregorianCalendar格式日期（解决XFire的日期格式和Date日期格式不一致）
	 * @param date
	 * @return
	 * @throws Exception
	 */
	public javax.xml.datatype.XMLGregorianCalendar getXMLGregorianCalendar(java.util.Date date) throws Exception {
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(date);
		javax.xml.datatype.DatatypeFactory dtf = javax.xml.datatype.DatatypeFactory.newInstance();
		return dtf.newXMLGregorianCalendar(calendar.get(calendar.YEAR),
				calendar.get(calendar.MONTH) + 1, calendar.get(calendar.DAY_OF_MONTH), calendar
						.get(calendar.HOUR), calendar.get(calendar.MINUTE),
				calendar.get(calendar.SECOND), calendar.get(calendar.MILLISECOND), calendar
						.get(calendar.ZONE_OFFSET) / (1000 * 60));
	}
	
	/**
	 * 根据XMLGregorianCalendar格式日期获取Date格式日期（解决XFire的日期格式和Date日期格式不一致）
	 * @param date
	 * @return
	 * @throws Exception
	 */
	public Date getDateFromXMLGregorianCalendar(XMLGregorianCalendar cal) {
		return cal.toGregorianCalendar().getTime();
	}
	
	/**
	 * 格式化xml
	 * @param document
	 * @param charset
	 * @return
	 */
	public  String formatXML(Document document, String charset) { 
        OutputFormat format = OutputFormat.createPrettyPrint(); 
        format.setEncoding(charset); 
        //format= OutputFormat.createCompactFormat();
        StringWriter sw = new StringWriter(); 
        XMLWriter xw = new XMLWriter(sw, format); 
        try { 
                xw.write(document); 
                xw.flush(); 
                xw.close(); 
        } catch (IOException e) { 
                log.error("格式化XML文档发生异常，请检查！" , e); 
        } 
        return sw.toString(); 
	}
}
